package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

func main() {
	var (
		err error
		db  *sql.DB
	)
	db, err = sql.Open("mysql", "root:123456@tcp(localhost:3306)/go-sql")
	if err != nil {
		log.Fatalf("open db err: %s", err.Error())
	}
	// ping
	err = db.Ping()
	if err != nil {
		log.Fatalf("ping db err: %s", err.Error())
	}
	// 新建 table
	var createStudentSql = "CREATE TABLE student(" +
		"id INT(10) NOT NULL AUTO_INCREMENT," +
		"name VARCHAR(64) NULL DEFAULT NULL," +
		"age INT(10) DEFAULT NULL,PRIMARY KEY (id))" +
		"ENGINE=InnoDB DEFAULT CHARSET=utf8;"
	// var res sql.Result
	_, err = db.Exec(createStudentSql)
	if err != nil {
		log.Fatalf("Exec createStudentSql err: %s", err.Error())
	}
	// 插入数据
	_, err = db.Exec("INSERT INTO student(name,age) VALUES (?,?)", "pedro", 25)
	if err != nil {
		log.Fatalf("Exec insert err: %s", err.Error())
	}
	// 执行查询
	var rows *sql.Rows
	rows, err = db.Query("SELECT * FROM student where name = ?", "pedro")
	if err != nil {
		log.Fatalf("Exec select err: %s", err.Error())
	}
	for rows.Next() {
		var (
			id   int
			name string
			age  int
		)
		if err = rows.Scan(&id, &name, &age); err != nil {
			log.Printf("Exec Rows Scan err: %s", err.Error())
		}
		log.Printf("id->%d,name->%s,age->%d\n", id, name, age)
	}
	// 更新
	_, err = db.Exec(`UPDATE student SET age = 65 WHERE name = "pedro"`)
	if err != nil {
		log.Fatalf("Exec update err: %s", err.Error())
	}
	// 执行查询
	rows, err = db.Query("SELECT * FROM student where name = ?", "pedro")
	if err != nil {
		log.Fatalf("Exec select err: %s", err.Error())
	}
	for rows.Next() {
		var (
			id   int
			name string
			age  int
		)
		if err = rows.Scan(&id, &name, &age); err != nil {
			log.Printf("Exec Rows Scan err: %s", err.Error())
		}
		log.Printf("id->%d,name->%s,age->%d\n", id, name, age)
	}
	// 删除
	_, err = db.Exec(`DELETE FROM student WHERE name = "pedro"`)
	if err != nil {
		log.Fatalf("Exec delete err: %s", err.Error())
	}
	log.Println("all finished!")
}
